import datetime
import os
import sys

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from typing import List
from src.tasks.enterprise.EntEnterprise import EntEnterprise
from src.utils import CommonUtils, BatchUtils


class SyncAbsDeals:
    def __init__(self, logger, localDb, absDb):
        self.logger = logger
        self.localDb = localDb
        self.absDb = absDb

    def __deleteAbsDeals__(self):
        sql = """delete from abs_deals_v2 """
        return self.localDb.ExecNoneQuery(sql)

    def __queryDataInfoByDate__(self, start, end):
        sql = """
            SELECT ad.*,GROUP_CONCAT(at.tag) as tag FROM abs_deals ad left join abs_deals_tag at on ad.dealid=at.dealid and at.isDeleted=0 WHERE ad.isdeleted=0 group by ad.DealId order by FirstInterestAccrualDate desc,ad.DealId LIMIT  %s, %s
        """
        return self.absDb.Query(sql, (start, end))

    def __updateLocal__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_deals_v2` (`ID`, `ModifyTime`, `IsDeleted`, `DealId`, `Name`, `ShortName`, `TotalOffering`, `CutOffDate`, `FirstInterestAccrualDate`, `FirstPaymentDate`, `LegalMaturityDate`, `Exchange`, `Regulator`, `IsReinvestment`, `ReinvestmentEndDate`, `IssueType`, `Status`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `EquityPercentage`, `Frequency`, `DayRule`, `FundUses`, `LastPaymentDate`, `NextPaymentDate`, `ActualFinancier`, `CreditProvider`, `HasCreditEnhancement`, `Originator`, `Manager`, `Underwriter`, `UnderwriterDeputyLead`, `UnderwriterJointLead`, `UnderwriterManagingLead`, `RatingAgency`, `AccountingConsultant`, `LegalAdvisor`, `Trustee`, `EvaluationAgency`, `Servicer`, `Guarantor`, `DeficiencyAgreementPromiser`, `SettlementDate`, `RegisterNumber`,`tag` ) values  
         (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # # 产品大全
    def __updAbsDealsInfo__(self):
        flag = 0
        count = 0
        self.__deleteAbsDeals__()
        # 加载需要同步的数据

        dataInfo = self.__queryDataInfoByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            # self.__delDataIds__(delDatas)
            BatchUtils.batchInsert(self.__updateLocal__, updateDatas)
            dataInfo = self.__queryDataInfoByDate__(count, 50000)
            count = count + 50000

    def __queryAbsDealsPropertyInfo__(self):
        sql = """
        SELECT ad.*, SUM(CASE WHEN b.`Name` = '资产笔数' THEN b.`Value` END) AS 'TotalNumberAssets', SUM(CASE WHEN b.`Name` = '资产户数' THEN b.`Value` END) AS 'NumberBorrowers', SUM(CASE WHEN b.`Name` = '合同金额-单笔-总计' THEN b.`Value` END) AS 'TotalContractAmount', SUM(CASE WHEN b.`Name` = '本金余额-单笔-总计' THEN b.`Value` END) AS 'TotalOutstandingPrincipalAmount', SUM(CASE WHEN b.`Name` = '本息余额-单笔-总计' THEN b.`Value` END) AS 'TotalOutstandingPrincipalAndInterest', SUM(CASE WHEN b.`Name` = '本金余额-单户-平均' THEN b.`Value` END) AS 'AveragePrincipalBalanceOfTheBorrower', SUM(CASE WHEN b.`Name` = '本金余额-单笔-最大值' THEN b.`Value` END) AS 'MaximumPrincipalBalanceOfASingleLoan', SUM(CASE WHEN b.`Name` = '本金余额-单笔-最小值' THEN b.`Value` END) AS 'MinimumPrincipalBalanceOfASingleLoan', SUM(CASE WHEN b.`Name` = '本金余额-单笔-平均' THEN b.`Value` END) AS 'AveragePrincipalBalanceOfASingleLoan', SUM(CASE WHEN b.`Name` = '合同金额-单笔-最大值' THEN b.`Value` END) AS 'MaximumContractAmountForASingleLoan', SUM(CASE WHEN b.`Name` = '合同金额-单笔-平均' THEN b.`Value` END) AS 'AverageContractAmountOfASingleLoan', SUM(CASE WHEN b.`Name` = '合同期限-单笔-加权平均' THEN b.`Value` END) AS 'WeightedAverageContractTerm', SUM(CASE WHEN b.`Name` = '剩余期限-单笔-加权平均' THEN b.`Value` END) AS 'WeightedAverageResidualMaturity', SUM(CASE WHEN b.`Name` = '账龄-单笔-加权平均' THEN b.`Value` END) AS 'WeightedAverageAging', SUM(CASE WHEN b.`Name` = '剩余期限-单笔-最大值' THEN b.`Value` END) AS 'MaximumRemainingMaturityOfASingleLoan', SUM(CASE WHEN b.`Name` = '剩余期限-单笔-最小值' THEN b.`Value` END) AS 'MinimumRemainingMaturityOfASingleLoan', SUM(CASE WHEN b.`Name` = '利率-单笔-加权平均(%)' THEN b.`Value` END) AS 'WeightedAverageAnnualLoanInterestRate', SUM(CASE WHEN b.`Name` = '利率-单笔-最大值(%)' THEN b.`Value` END) AS 'MaximumAnnualInterestRateForASingleLoan', SUM(CASE WHEN b.`Name` = '利率-单笔-最小值(%)' THEN b.`Value` END) AS 'MinimumAnnualInterestRateForASingleLoan', SUM(CASE WHEN b.`Name` = '抵押物初始评估总价值' THEN b.`Value` END) AS 'TotalValueOfCollateralEvaluation', SUM(CASE WHEN b.`Name` = '加权平均贷款初始抵押率/LTV(%)' THEN b.`Value` END) AS 'LTV', SUM(CASE WHEN b.`Name` = '抵押住房新房占比(%)' THEN b.`Value` END) AS 'FirstHandRateOfCollateral', SUM(CASE WHEN b.`Name` = '借款人加权平均年龄(岁)' THEN b.`Value` END) AS 'WeightedAverageAgeOfBorrowers', SUM(CASE WHEN b.`Name` = '借款人加权平均年收入' THEN b.`Value` END) AS 'WeightedAverageAnnualIncomeOfBorrowers', SUM(CASE WHEN b.`Name` = '借款人加权平均收入债务比(%)' THEN b.`Value` END) AS 'WeightedAverageIncomeToDebtRatioOfBorrowers', SUM(CASE WHEN b.`Name` = '30-40岁借款人贷款占比(%)' THEN b.`Value` END) AS 'TheProportionOfTheBorrowersLoanAmount'
        FROM (
        SELECT a.ShortName, a.`Name`, a.CNABSCategory1, a.CNABSCategory2, a.CNABSCategory3, a.`Status`, a.FirstInterestAccrualDate, a.RatingAgency, a.Originator, a.ActualFinancier, a.Servicer, a.CreditProvider, a.DealId, a.ID, GROUP_CONCAT(adt.tag) as tag
        FROM abs_deals a FORCE INDEX (FirstInterestAccrualDate)
        LEFT JOIN abs_deals_tag adt ON a.DealId = adt.DealId
        WHERE a.IsDeleted = 0
        GROUP BY a.DealId
        ORDER BY adt.tag desc, FirstInterestAccrualDate DESC,a.DealId DESC) ad
        LEFT JOIN abs_assets_statistics b ON ad.DealId = b.DealId AND b.PaymentDate IS NULL
        GROUP BY ad.DealId  
                  """
        return self.absDb.Query(sql)

    def __updateAbsDealsPropertyInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_deals_pool_v2` (`ShortName`, `Name`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `Status`, `FirstInterestAccrualDate`, `RatingAgency`, `Originator`, `ActualFinancier`, `Servicer`, `CreditProvider`, `DealId`, `ID`, `tag`, `TotalNumberAssets`, `NumberBorrowers`, `TotalContractAmount`, `TotalOutstandingPrincipalAmount`, `TotalOutstandingPrincipalAndInterest`, `AveragePrincipalBalanceOfTheBorrower`, `MaximumPrincipalBalanceOfASingleLoan`, `MinimumPrincipalBalanceOfASingleLoan`, `AveragePrincipalBalanceOfASingleLoan`, `MaximumContractAmountForASingleLoan`, `AverageContractAmountOfASingleLoan`, `WeightedAverageContractTerm`, `WeightedAverageResidualMaturity`, `WeightedAverageAging`, `MaximumRemainingMaturityOfASingleLoan`, `MinimumRemainingMaturityOfASingleLoan`, `WeightedAverageAnnualLoanInterestRate`, `MaximumAnnualInterestRateForASingleLoan`, `MinimumAnnualInterestRateForASingleLoan`, `TotalValueOfCollateralEvaluation`, `LTV`, `FirstHandRateOfCollateral`, `WeightedAverageAgeOfBorrowers`, `WeightedAverageAnnualIncomeOfBorrowers`, `WeightedAverageIncomeToDebtRatioOfBorrowers`, `TheProportionOfTheBorrowersLoanAmount`) values  
         (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def __deletePool__(self):
        sql = """delete from abs_deals_pool_v2 """
        return self.localDb.ExecNoneQuery(sql)


    # # 入池资产特征
    def __updAbsDealsProperty__(self):
        self.__deletePool__()
        dataInfo = self.__queryAbsDealsPropertyInfo__()
        updateDatas = []
        for r in dataInfo:
            updateDatas.append(list(r.values()))
        BatchUtils.batchInsert(self.__updateAbsDealsPropertyInfo__, updateDatas)

    def __queryAbsDealsDisbSyncTimeLine__(self):
        sql = """SELECT DATE_FORMAT(ModifyTime, '%Y-%m-%d %H:%i:%s') AS update_time, id  from abs_deals_Disb_v2 
                 order by ModifyTime desc LIMIT 1 """
        return self.localDb.QueryOne(sql)

    def __queryAbsDealsDisbDataInfo__(self):
        sql = """
with adt as ( select DISTINCT ad_.DealId from abs_deals ad_ left join abs_deals_tag adt_ on ad_.DealId = adt_.DealId and adt_.IsDeleted = 0 where ad_.IsDeleted = 0 ), a as ( select maad.ID, mad.ID as adId from abs_deals mad , abs_assets_distribution maad , adt where mad.IsDeleted = 0 and maad.IsDeleted = 0 and mad.DealId = maad.DealId and adt.DealId = mad.DealId order by FirstInterestAccrualDate desc,ID desc limit 0,50000 ) select concat(ad.DealId, '|', aad.ID) as dstbId, ad.ShortName as shortName, ad.Name as name, ad.CNABSCategory1 as cnabsCategory1, ad.CNABSCategory2 as cnabsCategory2, ad.CNABSCategory3 as cnabsCategory3, ad.Status as status, ad.FirstInterestAccrualDate as firstInterestAccrualDate, ad.Originator as originator, ad.ActualFinancier as actualFinancier, ad.Servicer as servicer, ad.CreditProvider as creditProvider, aad.Name as dstbType, aad.SubName as dstbSubType, aad.`section`, aad.assetCount, aad.countPercent, aad.balance, aad.balancePercent from a left join abs_deals ad on a.adId = ad.ID left join abs_assets_distribution aad on a.ID = aad.ID 
                  """
        return self.absDb.Query(sql)

    def __queryAbsDealsDisbDataInfoByDate__(self,start ,end):
        sql = """
WITH adt AS (
SELECT DISTINCT ad_.DealId, GROUP_CONCAT(adt_.Tag) as Tag
FROM abs_deals ad_
LEFT JOIN abs_deals_tag adt_ ON ad_.DealId = adt_.DealId AND adt_.IsDeleted = 0 GROUP BY ad_.DealId), a AS (
SELECT maad.ID, mad.ID AS adId, adt.Tag
FROM abs_deals mad, abs_assets_distribution maad, adt
WHERE mad.IsDeleted = 0 AND maad.IsDeleted = 0 AND mad.DealId = maad.DealId AND adt.DealId = mad.DealId
ORDER BY FirstInterestAccrualDate DESC,ID DESC
LIMIT %s,%s)
SELECT aad.id, aad.ModifyTime, CONCAT(ad.DealId, '|', aad.ID) AS dstbId, ad.ShortName AS shortName, ad.Name AS name, ad.CNABSCategory1 AS cnabsCategory1, ad.CNABSCategory2 AS cnabsCategory2, ad.CNABSCategory3 AS cnabsCategory3, ad.Status AS STATUS, ad.FirstInterestAccrualDate AS firstInterestAccrualDate, ad.Originator AS originator, ad.ActualFinancier AS actualFinancier, ad.Servicer AS servicer, ad.CreditProvider AS creditProvider, aad.Name AS dstbType, aad.SubName AS dstbSubType, aad.`section`, aad.assetCount, aad.countPercent, aad.balance, aad.balancePercent, a.Tag
FROM a
LEFT JOIN abs_deals ad ON a.adId = ad.ID
LEFT JOIN abs_assets_distribution aad ON a.ID = aad.ID
        """
        return self.absDb.Query(sql, (start, end))

    def __updateAbsDealsDisbDataInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_deals_disb_v2` (`id`, `ModifyTime`, `dstbId`, `shortName`, `name`, `cnabsCategory1`, `cnabsCategory2`, `cnabsCategory3`, `STATUS`, `firstInterestAccrualDate`, `originator`, `actualFinancier`, `servicer`, `creditProvider`, `dstbType`, `dstbSubType`, `section`, `assetCount`, `countPercent`, `balance`, `balancePercent`,tag)  values  
         (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def __deleteAbsDealsDisb__(self):
        sql = """delete from abs_deals_disb_v2 where sync_time <= DATE_SUB(NOW(),INTERVAL 10 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    # # 入池资产分布
    def __updAbsDealsDisb__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryAbsDealsDisbDataInfoByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateAbsDealsDisbDataInfo__, updateDatas)
            dataInfo = self.__queryAbsDealsDisbDataInfoByDate__(count,50000)
            count = count + 50000
        self.__deleteAbsDealsDisb__()

    def __queryBondsDataInfoByDate__(self, start, end):
        sql = """
            SELECT ab.* ,ad.Name as dealName,ad.ShortName as dealShortName, ad.Status,ad.CNABSCategory1,ad.CNABSCategory2 ,ad.CNABSCategory3 ,ad.IssueType ,ad.Originator ,ad.ActualFinancier ,ad.CreditProvider ,ad.Servicer,ad.TotalOffering,ad.Manager,ad.LegalMaturityDate,ad.CutOffDate, GROUP_CONCAT(at.tag) as tag from abs_bonds ab left join abs_deals ad on ab.DealId=ad.DealId 
            left join abs_deals_tag at on at.DealId=ab.DealId and at.IsDeleted=0 where ab.IsDeleted =0 and ad.IsDeleted =0 group by ad.DealId,ab.BondId order by ab.FirstInterestAccrualDate desc,ad.DealId,ab.BondId LIMIT %s , %s
        """
        return self.absDb.Query(sql, (start, end))

    def __updateBondsDatas__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_bonds_v2` (`ID`,`ModifyTime`,`IsDeleted`,`DealId`, `BondId`, `TrancheCode`, `BondCode`, `Name`, `ShortName`, `Exchange`, `IssueAmount`, `BondType`, `RepaymentType`, `AccrualMethod`, `Coupon`, `CouponType`, `BaseRateName`, `BaseRate`, `Spread`, `Frequency`, `Currency`, `InitialTranchePercentage`, `FirstInterestAccrualDate`, `ListedToTransferDate`, `ActualMaturityDate`, `EstimatedMaturityDate`, `TermString`, `Term`, `InitialFaceValue`, `InitialPrice`, `IssueBeginDate`, `IssueEndDate`, `InitialWal`, `InitialWalLegal`, `InitialReferSpread`, `InitialReferBaseRate`, `InitialReferBaseRateName`, `HasCreditEnhancement`, `OriginalRating`, `OriginalRatingAgency`, `CurrentRating`, `CurrentRatingAgency`, `LatestPaymentDate`, `CurrentPrincipalBalance`, `CurrentTranchePercentage`, `CurrentFaceValue`, `CurrentCoupon`, `CurrentBaseCoupon`, `CurrentRemainingTerm`, `CurrentWAL`, `dealName`, `dealShortName`, `Status`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `IssueType`, `Originator`, `ActualFinancier`, `CreditProvider`, `Servicer`, `TotalOffering`, `Manager`, `LegalMaturityDate`, `CutOffDate`, `tag`) values  
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)


    def __deleteBondsDatas__(self):
        sql = """delete from abs_bonds_v2 """
        return self.localDb.ExecNoneQuery(sql)

    # # 证券大全
    def __updAbsBonds__(self):
        flag = 0
        count = 0
        self.__deleteBondsDatas__()

        dataInfo = self.__queryBondsDataInfoByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            # delDatas = []
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            # self.__delDataIds__(delDatas)
            BatchUtils.batchInsert(self.__updateBondsDatas__, updateDatas)
            dataInfo = self.__queryBondsDataInfoByDate__(count,50000)
            count = count + 50000


    def __deleteNonPerformingDatas__(self):
        sql = """delete from abs_non_performing_v2 """
        return self.localDb.ExecNoneQuery(sql)


    def __queryNonPerformingByDate__(self ,start ,end):
        sql = """
            WITH aa AS(
            SELECT DealId,Value
            FROM abs_assets_statistics
            WHERE PaymentDate IS NULL AND IsDeleted=0 AND Name = '本金余额-单笔-总计'), bb AS (
            SELECT DealId,Value
            FROM abs_assets_statistics
            WHERE PaymentDate IS NULL AND IsDeleted=0 AND Name = '本息余额-单笔-总计'), cc AS (
            SELECT aa.dealid,aa.value AS AssetInitPrincipal,bb.value AS AssetInitPrincipalInterest
            FROM aa
            JOIN bb ON aa.dealid=bb.dealid), d1 AS (
            SELECT ab.dealid, SUM(ab.IssueAmount) AS 优先级, ab.BondType,ab.IssueAmount
            FROM abs_bonds ab
            WHERE ab.isdeleted=0 AND ab.BondType = '优先'
            GROUP BY ab.dealid), d3 AS (
            SELECT ab.dealid, SUM(ab.IssueAmount) AS 次级
            FROM abs_bonds ab
            WHERE ab.isdeleted=0 AND ab.BondType = '劣后'
            GROUP BY ab.dealid), dd AS (
            SELECT d1.dealid,d1.优先级,d3.次级
            FROM d1
            LEFT JOIN d3 ON d1.dealid=d3.dealid), ee AS (
            SELECT dealid, Tag AS Tag
            FROM abs_deals_tag
            WHERE IsDeleted=0), cf AS (
            SELECT dealid, PaymentDate AS PaymentDate, CollectionDate AS CollectionDate, Balance, Amount, DuringDisposal, DisposalDone,LiquidityAssistance,CleanupRepurchase, Other, InvestIncome
            FROM abs_assets_cashflows_npl
            WHERE IsDeleted=0), cf1 AS (
            SELECT dealid, BalanceBegin, PaymentDate AS PaymentDate
            FROM abs_assets_collection_cfs_ext ab
            WHERE isdeleted=0)
            SELECT CONCAT(ad.dealid,'-', IFNULL(cf.PaymentDate,'yakeokosu')) AS rownum, ad.dealid, ad.Name,ad.ShortName,ad.Originator,ad.FirstInterestAccrualDate,ad.TotalOffering,ad.CNABSCategory1, ad.CNABSCategory2,ad.CNABSCategory3, ad.`Status`, cf.PaymentDate, cf.CollectionDate,if(cc.AssetInitPrincipal=0,'',cc.AssetInitPrincipal) AS AssetInitPrincipal,cc.AssetInitPrincipalInterest, cf.Balance, cf.Amount, cf.DuringDisposal,cf.DisposalDone,cf.LiquidityAssistance,cf.CleanupRepurchase, cf.Other, cf.InvestIncome, dd.优先级 AS IssueAmountFirst,dd.次级 AS IssueAmountSecond, ad.ActualFinancier, ad.Servicer, ad.CreditProvider, GROUP_CONCAT(ee.tag) as tag, cf1.BalanceBegin
            FROM cf
            LEFT JOIN cc ON cf.dealid=cc.dealid
            LEFT JOIN dd ON cf.dealid=dd.dealid
            LEFT JOIN abs_deals ad ON ad.dealid=cf.dealid
            LEFT JOIN ee ON cf.dealid=ee.dealid
            LEFT JOIN cf1 ON cf.dealid=cf1.dealid AND cf.PaymentDate = cf1.PaymentDate
            WHERE ad.isdeleted=0 AND CNABSCategory2='不良资产重组'
            GROUP BY rownum
            ORDER BY cf.PaymentDate DESC,rownum
            LIMIT %s , %s
                  """
        return self.absDb.Query(sql,(start, end))

    def __updateNonPerformingDatas__(self, updateDatas):
        sql = """
        INSERT INTO `abs_non_performing_v2` (`rownum`, `dealid`, `Name`, `ShortName`, `Originator`, `FirstInterestAccrualDate`, `TotalOffering`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `Status`, `PaymentDate`, `CollectionDate`, `AssetInitPrincipal`, `AssetInitPrincipalInterest`, `Balance`, `Amount`, `DuringDisposal`, `DisposalDone`, `LiquidityAssistance`, `CleanupRepurchase`, `Other`, `InvestIncome`, `IssueAmountFirst`, `IssueAmountSecond`, `ActualFinancier`, `Servicer`, `CreditProvider`, `Tag`, `BalanceBegin`)  values  
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # # 不良类
    def __updNonPerforming__(self):
        flag = 0
        count = 0
        self.__deleteNonPerformingDatas__()

        dataInfo = self.__queryNonPerformingByDate__(count ,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateNonPerformingDatas__, updateDatas)
            dataInfo = self.__queryNonPerformingByDate__(count ,50000)
            count = count + 50000


    def __deleteCreditorRights__(self):
        sql = """delete from abs_creditor_rights_v2 where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryCreditorRightsByDate__(self, start, end):
            sql = """
            SELECT oo.*, aap.CumulativeDefault, aap.CumulativeDefaultRate_Reported, aap.CumulativePrepay, aap.CumulativePrepayRate, aap.CDR, aap.CPR, aap.AssetCount, aap.WeightedAverageRate, aap.WeightedAverageRemainingTerm, aas.Value as 'initAssetPoolAmount', sum(case when aad.`Section` = '正常' then aad.Balance end) as m0, sum(case when aad.`Section` = '正常' then aad.BalancePercent end) as m0rate, sum(case when aad.`Section` = '正常' then aad.AssetCount end) as m0Count, sum(case when aad.`Section` = '拖欠1至30天' then aad.Balance end) as m1, sum(case when aad.`Section` = '拖欠1至30天' then aad.BalancePercent end) as m1rate, sum(case when aad.`Section` = '拖欠1至30天' then aad.AssetCount end) as m1Count, sum(case when aad.Section = '拖欠31至60天' then aad.Balance*10000 end) as m2, sum(case when aad.Section = '拖欠31至60天' then aad.BalancePercent end) as m2rate, sum(case when aad.`Section` = '拖欠31至60天' then aad.AssetCount end) as m2Count, sum(case when aad.Section = '拖欠61至90天' then aad.Balance*10000 end) as m3, sum(case when aad.Section = '拖欠61至90天' then aad.BalancePercent end) as m3rate, sum(case when aad.`Section` = '拖欠61至90天' then aad.AssetCount end) as m3Count,
             sum(case when aad.Section = '拖欠31至90天' then aad.Balance*10000 end) as m7, sum(case when aad.Section = '拖欠31至90天' then aad.BalancePercent end) as m7rate, sum(case when aad.`Section` = '拖欠31至90天' then aad.AssetCount end) as m7Count, sum(case when aad.Section = '拖欠91至120天' then aad.Balance*10000 end) as m4, sum(case when aad.Section = '拖欠91至120天' then aad.BalancePercent end) as m4rate, sum(case when aad.`Section` = '拖欠91至120天' then aad.AssetCount end) as m4Count, sum(case when aad.Section = '拖欠121至150天' then aad.Balance*10000 end) as m5, sum(case when aad.Section = '拖欠121至150天' then aad.BalancePercent end) as m5rate, sum(case when aad.`Section` = '拖欠121至150天' then aad.AssetCount end) as m5Count, sum(case when aad.Section = '拖欠151至180天' then aad.Balance*10000 end) as m6, sum(case when aad.Section = '拖欠151至180天' then aad.BalancePercent end) as m6rate, sum(case when aad.`Section` = '拖欠151至180天' then aad.AssetCount end) as m6Count, sum(case when aad.Section = '拖欠91至180天' then aad.Balance*10000 end) as m8, sum(case when aad.Section = '拖欠91至180天' then aad.BalancePercent end) as m8rate, sum(case when aad.`Section` = '拖欠91至180天' then aad.AssetCount end) as m8Count, 
             sum(case when aad.Section = '拖欠90天以上' then aad.Balance*10000 end) as m3plus, sum(case when aad.Section = '拖欠90天以上' then aad.BalancePercent end) as m3plusrate, sum(case when aad.`Section` = '拖欠90天以上' then aad.AssetCount end) as m3plusCount, sum(case when aad.Section = '拖欠120天以上' then aad.Balance*10000 end) as m4plus, sum(case when aad.Section = '拖欠120天以上' then aad.BalancePercent end) as m4plusrate, sum(case when aad.`Section` = '拖欠120天以上' then aad.AssetCount end) as m4plusCount, sum(case when aad.Section = '拖欠150天以上' then aad.Balance*10000 end) as m5plus, sum(case when aad.Section = '拖欠150天以上' then aad.BalancePercent end) as m5plusrate, sum(case when aad.`Section` = '拖欠150天以上' then aad.AssetCount end) as m5plusCount, sum(case when aad.Section = '拖欠180天以上' then aad.Balance*10000 end) as m6plus, sum(case when aad.Section = '拖欠180天以上' then aad.BalancePercent end) as m6plusrate, sum(case when aad.`Section` = '拖欠180天以上' then aad.AssetCount end) as m6plusCount, sum(case when aad.Section = '违约贷款(未被核销)' then aad.Balance*10000 end) as defaultamt, sum(case when aad.Section = '违约贷款(未被核销)' then aad.BalancePercent end) as defaultamtrate, sum(case when aad.Section = '违约贷款(未被核销)' then aad.AssetCount end) as defaultamtCount 
             from ( select ad.ShortName , ad.DealId , ad.Name, ad.CNABSCategory1, ad.CNABSCategory2, ad.CNABSCategory3, ad.Status , ad.FirstPaymentDate , acc.PaymentDate, acc.CollectionDate, ad.TotalOffering, acc.Balance, acc.PrincipalAmount, acc.PrincipalSchedule, acc.PrincipalPrepayment, acc.PrincipalDefaultRecovery, acc.PrincipalDelinqRecovery, acc.PrincipalDisposal, acc.PrincipalCleanupRepurchase, acc.PrincipalRedemption, acc.PrincipalOther, acc.PrincipalPreviousRetained, acc.InterestAmount, acc.InterestSchedule, acc.InterestPrepayment, acc.InterestDefaultRecovery, acc.InterestDelinqRecovery, acc.InterestDisposal, acc.InterestCleanupRepurchase, acc.InterestRedemption, acc.InterestOther, acc.InterestPreviousRetained, acc.InvestIncomePrincipal,acc.InvestIncomeInterest, acc.DefaultAmount, acc.ReinvestmentAmount, ad.Originator, ad.ActualFinancier, ad.Servicer, ad.CreditProvider, acc.PaymentDate as 'accPayMentDate', aacce.`Number`, if(aacce.BalanceBegin=0,null,aacce.BalanceBegin) as BalanceBegin, GROUP_CONCAT(at.tag) as tag, ad.FirstInterestAccrualDate from abs_deals ad left join abs_assets_collection_cashflows acc FORCE INDEX ( abs_assets_collection_cashflows_DealId_IDX ) on ad.DealId = acc.DealId left join abs_deals_tag at on ad.dealid=at.dealid and at.isDeleted=0 left join abs_assets_collection_cfs_ext aacce on aacce.DealId =ad.DealId and aacce.PaymentDate =acc.PaymentDate 
             where ad.IsDeleted = 0 and acc.IsDeleted = 0 and ad.CNABSCategory2 !='不良资产重组' group by ad.DealId,acc.PaymentDate order by ad.FirstPaymentDate desc,ad.DealId,acc.PaymentDate limit %s,%s ) oo left join abs_assets_distribution aad on aad.DealId = oo.DealId and oo.accPayMentDate = aad.PaymentDate and aad.PaymentDate is not null and aad.Name = '贷款逾期时间分布' and aad.IsDeleted = 0 left join abs_assets_performance aap on aap.DealId = oo.DealId and aap.PaymentDate = oo.accPayMentDate and aap.IsDeleted = 0 left join abs_assets_statistics aas on aas.DealId = oo.DealId and aas.PaymentDate is null and aas.Name='本金余额-单笔-总计' and aas.IsDeleted = 0 group by oo.accPayMentDate,oo.DealId order by oo.FirstPaymentDate desc, oo.DealId, oo.PaymentDate 
            """
            return self.absDb.Query(sql, (start, end))

    def __updateCreditorRightsDataInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_creditor_rights_v2` (`ShortName`, `DealId`, `Name`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `Status`, `FirstPaymentDate`, `PaymentDate`, `CollectionDate`, `TotalOffering`, `Balance`, `PrincipalAmount`, `PrincipalSchedule`, `PrincipalPrepayment`, `PrincipalDefaultRecovery`, `PrincipalDelinqRecovery`, `PrincipalDisposal`, `PrincipalCleanupRepurchase`, `PrincipalRedemption`, `PrincipalOther`, `PrincipalPreviousRetained`, `InterestAmount`, `InterestSchedule`, `InterestPrepayment`, `InterestDefaultRecovery`, `InterestDelinqRecovery`, `InterestDisposal`, `InterestCleanupRepurchase`, `InterestRedemption`, `InterestOther`, `InterestPreviousRetained`, `InvestIncomePrincipal`, `InvestIncomeInterest`, `DefaultAmount`, `ReinvestmentAmount`, `Originator`, `ActualFinancier`, `Servicer`, `CreditProvider`, `accPayMentDate`, `Number`, `BalanceBegin`, `tag`, `FirstInterestAccrualDate`, `CumulativeDefault`, `CumulativeDefaultRate_Reported`, `CumulativePrepay`, `CumulativePrepayRate`, `CDR`, `CPR`, `AssetCount`, `WeightedAverageRate`, `WeightedAverageRemainingTerm`, `initAssetPoolAmount`, `m0`, `m0rate`, `m0Count`, `m1`, `m1rate`, `m1Count`, `m2`, `m2rate`, `m2Count`, `m3`, `m3rate`, `m3Count`, `m7`, `m7rate`, `m7Count`, `m4`, `m4rate`, `m4Count`, `m5`, `m5rate`, `m5Count`, `m6`, `m6rate`, `m6Count`, `m8`, `m8rate`, `m8Count`, `m3plus`, `m3plusrate`, `m3plusCount`, `m4plus`, `m4plusrate`, `m4plusCount`, `m5plus`, `m5plusrate`, `m5plusCount`, `m6plus`, `m6plusrate`, `m6plusCount`, `defaultamt`, `defaultamtrate`, `defaultamtCount`) VALUES
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,  %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,  %s, %s, %s, %s, %s, %s, %s, %s, %s,  %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # # 债权类
    def __updCreditorRights__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryCreditorRightsByDate__(count,5000)
        count = count + 5000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 5000:
                flag = 1
            BatchUtils.batchInsert(self.__updateCreditorRightsDataInfo__, updateDatas)
            dataInfo = self.__queryCreditorRightsByDate__(count,5000)
            count = count + 5000
        self.__deleteCreditorRights__()



    def __deleteCashFlows__(self):
        sql = """delete from abs_cash_flow_v2 where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryCashFlows__(self, start, end):
            sql = """
                SELECT CONCAT(ad.DealId,'#',ifnull(pc.PaymentDate,'Nal'),'#',pc.EndDate) AS id, ad.DealId, ad.`Name`, ad.ShortName, ad.CNABSCategory1, ad.CNABSCategory2, ad.CNABSCategory3, 
                ad.`status`, ad.Originator, ad.ActualFinancier, ad.Servicer, ad.CreditProvider, pc.PaymentDate, pc.BeginDate, pc.EndDate, pc.BeginningBalance, pc.Balance, pc.Principal, pc.Interest, 
                case pc.IsCombined when 1 then '是' when 0 then '否' END AS IsCombined, case pc.IsNotional when 1 then '是' when 0 then '否' END AS IsNotional , ad.isdeleted as adisdeleted , pc.isdeleted as pcisdeleted, pc.ModifyTime ,ad.id as adId, pc.id as cashId
                FROM abs_assets_promised_cashflows pc inner JOIN abs_deals ad ON pc.dealid=ad.dealid  ORDER BY pc.dealid,pc.paymentdate,pc.EndDate LIMIT %s , %s 
            """
            return self.absDb.Query(sql, (start, end))

    def __queryCashFlowsByDate__(self, time , start, end ):
            sql = """
                SELECT CONCAT(ad.DealId,'#',ifnull(pc.PaymentDate,'Nal'),'#',pc.EndDate) AS id, ad.DealId, ad.`Name`, ad.ShortName, ad.CNABSCategory1, ad.CNABSCategory2, ad.CNABSCategory3, 
                ad.`status`, ad.Originator, ad.ActualFinancier, ad.Servicer, ad.CreditProvider, pc.PaymentDate, pc.BeginDate, pc.EndDate, pc.BeginningBalance, pc.Balance, pc.Principal, pc.Interest, 
                case pc.IsCombined when 1 then '是' when 0 then '否' END AS IsCombined, case pc.IsNotional when 1 then '是' when 0 then '否' END AS IsNotional , ad.isdeleted as adisdeleted , pc.isdeleted as pcisdeleted, pc.ModifyTime ,ad.id as adId, pc.id as cashId
                FROM abs_assets_promised_cashflows pc inner JOIN abs_deals ad ON pc.dealid=ad.dealid where pc.ModifyTime >= %s ORDER BY pc.dealid,pc.paymentdate,pc.EndDate LIMIT %s , %s 
            """
            return self.absDb.Query(sql, (time, start, end))

    def __updateCashFlowsDataInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_cash_flow_v2` (`id`, `DealId`, `Name`, `ShortName`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `status`, `Originator`, `ActualFinancier`, `Servicer`, `CreditProvider`, `PaymentDate`, `BeginDate`, `EndDate`, `BeginningBalance`, `Balance`, `Principal`, `Interest`, `IsCombined`, `IsNotional`, `adisdeleted`, `pcisdeleted`, `ModifyTime`,`adId`,`cashId`)  VALUES
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def __querySyncTimeLine__(self):
        sql = """SELECT DATE_FORMAT(ModifyTime, '%Y-%m-%d %H:%i:%s') AS ModifyTime from abs_cash_flow_v2 
                 order by ModifyTime desc LIMIT 1 """
        return self.localDb.QueryOne(sql)

    def __updateInvalidData__(self):
        sql = """update abs_cash_flow_v2 set adisdeleted = 1 where adId not in (select id from abs_deals_v2 where IsDeleted = 0) """
        return self.localDb.QueryOne(sql)

    # # 归集现金流
    def __updCashFlows__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        timeData = self.__querySyncTimeLine__()
        if timeData is None:
            dataInfo = self.__queryCashFlows__(count,50000)
            count = count + 50000
            while flag == 0:
                updateDatas = []
                for r in dataInfo:
                    updateDatas.append(list(r.values()))
                if len(dataInfo) < 50000:
                    flag = 1
                BatchUtils.batchInsert(self.__updateCashFlowsDataInfo__, updateDatas)
                dataInfo = self.__queryCashFlows__(count,50000)
                count = count + 50000
        else:
            dataInfo = self.__queryCashFlowsByDate__(timeData["ModifyTime"], count, 50000)
            count = count + 50000
            while flag == 0:
                updateDatas = []
                for r in dataInfo:
                    updateDatas.append(list(r.values()))
                if len(dataInfo) < 50000:
                    flag = 1
                BatchUtils.batchInsert(self.__updateCashFlowsDataInfo__, updateDatas)
                timeData = self.__querySyncTimeLine__()
                dataInfo = self.__queryCashFlowsByDate__( timeData["ModifyTime"] ,count,50000)
                count = count + 50000
        self.__updateInvalidData__()

    # #  当期现金流
    def __updCurrentCash__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryCurrentCashByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateCurrentCashInfo__, updateDatas)
            dataInfo = self.__queryCurrentCashByDate__(count,50000)
            count = count + 50000
        self.__deleteCurrentCash__()


    def __deleteCurrentCash__(self):
        sql = """delete from abs_bond_duration_v2 where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryCurrentCashByDate__(self, start, end):
            sql = """
                WITH abc AS (
                SELECT ID
                FROM (
                SELECT ID, ROW_NUMBER() over(PARTITION BY BondId
                ORDER BY paymentDate DESC) AS rn
                FROM abs_bonds_cashflows
                WHERE IsDeleted = 0) a
                WHERE a.rn = 1), QUERY AS (
                SELECT DISTINCT ab.id, ab.BondId AS BondId, GROUP_CONCAT(adt.tag) as tag
                FROM abs_bonds ab
                LEFT JOIN abs_deals ad ON ab.DealId = ad.DealId AND ad.IsDeleted = 0
                LEFT JOIN abs_deals_tag adt ON ab.DealId = adt.DealId AND adt.IsDeleted = 0
                WHERE ab.IsDeleted = 0 GROUP BY ab.id), dp AS (
                SELECT aa.ID AS bdrtId, aa.BondId, aa.DealId, aa.PaymentDate AS paymentDate, aa.BeginningBalance AS beginningBalance, aa.Principal AS principal, aa.Interest AS interest, aa.Balance AS balance, ab.BondCode AS bondCode, ab.ShortName AS shortName, ab.Name AS bondName, ad.Name AS dealName, ab.Exchange AS exchange, ad.CNABSCategory1 AS cnabsCategory1, ad.CNABSCategory2 AS cnabsCategory2, ad.CNABSCategory3 AS cnabsCategory3, ad.Originator AS originator, ad.ActualFinancier AS actualFinancier, ad.Servicer AS servicer, ad.CreditProvider AS creditProvider
                , aa.principalPaidPercentage, aa.interestPaidPercentage, aa.balancePercentage, ad.ShortName AS dealShortName, QUERY.tag, ad.FirstInterestAccrualDate
                FROM abs_bonds_cashflows aa
                INNER JOIN abc ON aa.ID = abc.ID
                INNER JOIN QUERY USING(BondId)
                LEFT JOIN abs_bonds ab ON ab.BondId = aa.BondId AND ab.IsDeleted = 0
                LEFT JOIN abs_deals ad ON ad.DealId = aa.DealId AND ad.IsDeleted = 0)
                SELECT *
                FROM dp
                ORDER BY dp.paymentDate DESC,dp.bdrtId DESC
                LIMIT %s, %s 
            """
            return self.absDb.Query(sql, (start, end))

    def __updateCurrentCashInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_bond_duration_v2` (`bdrtId`, `BondId`, `DealId`, `paymentDate`, `beginningBalance`, `principal`, `interest`, `balance`, `bondCode`, `shortName`, `bondName`, `dealName`, `exchange`, `cnabsCategory1`, `cnabsCategory2`, `cnabsCategory3`, `originator`, `actualFinancier`, `servicer`, `creditProvider`, `principalPaidPercentage`, `interestPaidPercentage`, `balancePercentage`, `dealShortName`, `tag`, `FirstInterestAccrualDate`)
         VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)


    # #  二级成交
    def __updSecondary__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__querySecondaryByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateSecondaryInfo__, updateDatas)
            dataInfo = self.__querySecondaryByDate__(count,50000)
            count = count + 50000
        self.__deleteSecondary__()


    def __deleteSecondary__(self):
        sql = """delete from abs_secondary_list_v2 where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __querySecondaryByDate__(self, start, end):
            sql = """
                SELECT CONCAT(d.DealId,'-', IFNULL(t.ID,'')) AS DealId, t.id,b.BondCode,b.ShortName,b.`Name` AS SecuritiesName,d.`Name`,b.Exchange,t.Date,t.YesterdayReferPrice,t.`Open`,t.Highest,t.Lowest,t.Newest,t.WeightedAverage,t.Volumn,t.Amount,t.Count,t.YieldOfHighest,t.YieldOfLowest,t.YieldOfNewest,d.CNABSCategory1,d.CNABSCategory2,d.CNABSCategory3,d.Originator,d.ActualFinancier,d.Servicer,d.CreditProvider
                , d.dealId AS absDealId , b.FirstInterestAccrualDate, d.ShortName AS dealShortName ,d.DealId AS dealDealId , GROUP_CONCAT(adt.tag) as tag
                FROM abs_deals d
                LEFT JOIN abs_bonds b ON d.DealId = b.DealId AND d.IsDeleted = 0
                LEFT JOIN abs_bonds_trade t FORCE INDEX (DATE) ON b.BondId = t.BondId AND t.IsDeleted = 0
                LEFT JOIN abs_deals_tag adt ON d.DealId = adt.DealId
                WHERE b.IsDeleted = 0 AND t.Date IS NOT NULL
                GROUP BY b.BondId,t.ID
                ORDER BY t.Date DESC,d.DealId DESC
                LIMIT %s, %s 
            """
            return self.absDb.Query(sql, (start, end))

    def __updateSecondaryInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_secondary_list_v2` (`DealId`, `id`, `BondCode`, `ShortName`, `SecuritiesName`, `Name`, `Exchange`, `Date`, `YesterdayReferPrice`, `Open`, `Highest`, `Lowest`, `Newest`, `WeightedAverage`, `Volumn`, `Amount`, `Count`, `YieldOfHighest`, `YieldOfLowest`, `YieldOfNewest`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `Originator`, `ActualFinancier`, `Servicer`, `CreditProvider`,`absDealId` , `FirstInterestAccrualDate`, `dealShortName`,`dealDealId` , `tag`) 
         VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)


    # #  增信措施
    def __updCreditEnhancement__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryCreditEnhancementByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateCreditEnhancementInfo__, updateDatas)
            dataInfo = self.__queryCreditEnhancementByDate__(count,50000)
            count = count + 50000
        self.__deleteCreditEnhancement__()


    def __deleteCreditEnhancement__(self):
        sql = """delete from abs_credit_enhancements_v2 where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryCreditEnhancementByDate__(self, start, end):
            sql = """
                SELECT ac.ID, ad.DealId, ad.`Name`, ad.ShortName, ad.CNABSCategory1, ad.CNABSCategory2, ad.CNABSCategory3, ad.`Status`, ad.FirstInterestAccrualDate, ad.`Exchange`, ad.IssueType, ad.Originator, ad.ActualFinancier, ad.Servicer, ac.Category, ac.CreditSummary, ac.CreditOrganization, ac.CreditDescription, ad.CreditProvider
                FROM abs_credit_enhancements ac
                INNER JOIN abs_deals ad ON ac.DealId = ad.DealId AND ad.IsDeleted = 0
                WHERE ac.IsDeleted = 0
                ORDER BY ad.FirstInterestAccrualDate DESC
                LIMIT %s, %s 
            """
            return self.absDb.Query(sql, (start, end))

    def __updateCreditEnhancementInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_credit_enhancements_v2` (`ID`, `DealId`, `Name`, `ShortName`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `Status`, `FirstInterestAccrualDate`, `Exchange`, `IssueType`, `Originator`, `ActualFinancier`, `Servicer`, `Category`, `CreditSummary`, `CreditOrganization`, `CreditDescription`, `CreditProvider`) 
         VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)